/************************************************************************************/
/*  Program name CAPFNDIMP_USER.sql                                                 */
/*  Creater:  NIP Karen (PCCW - UniHub)                                             */
/*************   History   *************************                                */
/* 20 JAN, 2006   NIP Karen         First created                                   */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

ALTER TRIGGER FND_USER_CT1 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_USER';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';
	person_id_error	EXCEPTION;

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_USER;

	CURSOR Cur_G IS
	SELECT
		A.*,
		B.User_ID_N
	FROM
		CFND_USER			A,
		CRST_FND_USER_MAP	B
	WHERE	A.User_ID	=	B.User_ID;

	lv_User_ID		NUMBER;
	lv_Employee_ID	NUMBER;

BEGIN
	UPDATE
		FND_USER
	SET
		USER_NAME	=	CAPBRIMP_MAPPING.RenameStr(SUBSTR(USER_NAME,1,4), USER_NAME),
		DESCRIPTION	=	CAPBRIMP_MAPPING.RenameStr(SUBSTR(DESCRIPTION,1,4), DESCRIPTION)
	WHERE USER_ID IN
		(
		SELECT
			USER_ID
		FROM
			CRST_FND_USER
		);

	FOR C1 IN Cur_F LOOP
		BEGIN
			lv_User_ID	:=	FND_USER_PKG.CreateUserID(
				x_user_name						=>	C1.User_Name,
				x_owner							=>	'SEED',
				x_unencrypted_password			=>	C1.User_Name,
				x_session_number				=>	0,
				x_start_date					=>	C1.Start_Date,
				x_end_date						=>	C1.End_Date,
				x_last_logon_date				=>	NULL,
				x_description					=>	C1.Description,
				x_password_date					=>	NULL,
				x_password_accesses_left		=>	NULL,
				x_password_lifespan_accesses	=>	NULL,
				x_password_lifespan_days		=>	NULL,
				x_employee_id					=>	NULL,
				x_email_address					=>	NULL,
				x_fax							=>	NULL,
				x_customer_id					=>	NULL,
				x_supplier_id					=>	NULL
			);

			--IF	C1.Employee_Number	IS	NOT NULL	THEN
/*				SELECT
					PERSON_ID
				INTO
					lv_Employee_ID
				FROM
					PER_ALL_PEOPLE_F
				WHERE	Employee_Number	=	C1.Employee_Number
				AND		SYSDATE BETWEEN Effective_Start_Date AND Effective_End_Date;
*/
			IF	C1.Employee_number	IS	NOT NULL	THEN
			BEGIN
				SELECT 	person_id_n
      			INTO
					lv_Employee_ID
				FROM	crst_fnd_per_all_people_map x,
						FND_USER y
      			WHERE 	x.person_id = y.employee_id
      			and  y.USER_ID = c1.user_id;
      		EXCEPTION
      			WHEN OTHERS THEN
         			RAISE person_id_error;
      		END;
			
			
				FND_USER_PKG.UpdateUser(
					x_user_name					=>	C1.User_Name,
					x_owner						=>	'SEED',
					x_employee_id				=>	lv_Employee_ID
				);
			END IF;

			INSERT	INTO CRST_FND_USER_MAP	VALUES
			(
			C1.USER_ID,
			CAPBRIMP_MAPPING.RenameStr(SUBSTR(C1.USER_NAME,1,4), C1.USER_NAME),
			lv_User_ID,
			C1.USER_NAME
			);

		EXCEPTION
			WHEN person_id_error THEN
				DBMS_OUTPUT.Put_Line('Cannot find Person ID' );
				lv_Err_Count	:=	lv_Err_Count + 1;
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	BEGIN
		FOR C2 IN Cur_G LOOP
			INSERT INTO CFND_USER VALUES
			(
				C2.User_ID_N,
				CAPBRIMP_MAPPING.GetOrgMapping(C2.S_ORG_ID),
				CAPBRIMP_MAPPING.GetBGMapping(C2.S_BUSINESS_GROUP_ID)
			);
		END LOOP;
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.Put_Line(SQLERRM);
			lv_Err_Count	:=	lv_Err_Count + 1;
	END;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
	
END;
/
ALTER TRIGGER FND_USER_CT1 ENABLE;
